﻿using System;
using System.Linq;
using System.Web;
using System.Collections;
using System.Data.SqlClient;
using System.Configuration;
using PsychveyApp1.classes;

namespace PsychveyApp1.classes
{
    public class DBManager
    {

        public static UserAccount UserLogin(string username, string password)
        {
            UserAccount useraccount = new UserAccount();
            useraccount.username = "";

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM useraccount WHERE Username = @Username and Password = @Password";
                comm.Parameters.AddWithValue("@Username", username);
                comm.Parameters.AddWithValue("@Password", password);
                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    useraccount.userID = (string)dr["UserID"];
                    useraccount.username = (string)dr["Username"];
                    useraccount.password = (string)dr["Password"];
                    useraccount.retypePassword = (string)dr["RetypePassword"];
                    useraccount.email = (string)dr["Email"];
                    useraccount.expirationDateForPremium = (string)dr["ExpirationDateForPremium"];
                    useraccount.accountTypeSelected = (string)dr["AccountTypeSelected"];
                    useraccount.paymentStatus = (string)dr["PaymentStatus"];
                    useraccount.userFullName = (string)dr["UserFullName"];
                    useraccount.paymentID = (string)dr["PaymentID"];
                    useraccount.receiptID = (string)dr["ReceiptID"];
                    useraccount.planID = (string)dr["PlanID"];


                }//end while
                dr.Close();
                conn.Close();

            }
            catch (SqlException exception)
            {
                throw exception;
            }
            return useraccount;
        }

        public static UserAccount GetUserById(string userID)
        {
            UserAccount useraccount = new UserAccount();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from useraccount WHERE UserID='" + userID + "'";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    useraccount.userID = (string)dr["UserID"];
                    useraccount.username = (string)dr["Username"];
                    useraccount.password = (string)dr["Password"];
                    useraccount.retypePassword = (string)dr["RetypePassword"];
                    useraccount.email = (string)dr["Email"];
                    useraccount.expirationDateForPremium = (string)dr["ExpirationDateForPremium"];
                    useraccount.accountTypeSelected = (string)dr["AccountTypeSelected"];
                    useraccount.paymentStatus = (string)dr["PaymentStatus"];
                    useraccount.userFullName = (string)dr["UserFullName"];
                    useraccount.paymentID = (string)dr["PaymentID"];
                    useraccount.receiptID = (string)dr["ReceiptID"];
                    useraccount.planID = (string)dr["PlanID"];
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return useraccount;
        }

        //return Survey
        public static Survey GetSurveyById(string surveyID)
        {
            Survey survey = new Survey();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from survey WHERE SurveyID= @surveyID";
                comm.Parameters.AddWithValue("@surveyID", surveyID);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    survey.title = (string)dr["Title"];
                    survey.dateCreated = (string)dr["DateCreated"];
                    survey.dateModified = (string)dr["DateModified"];
                    survey.userID = (string)dr["UserID"];
                    survey.typeOfSurvey = (string)dr["TypeOfSurvey"];
                    survey.surveyID = (string)dr["SurveyID"];
                    survey.expirationDateOfSurvey = (string)dr["ExpirationDateOfSurvey"];
                    survey.noOfRespondents = (string)dr["NoOfRespondents"];
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return survey;
        }

        public static int InsertUser(UserAccount user)
        {
            int rowsinserted = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO useraccount(userid,username,password,retypepassword,email,expirationdateforpremium,accounttypeselected,paymentstatus,userfullname,paymentid,receiptid,planid)" +
                    " VALUES (@userID,@userName,@password,@retypePassword,@email,@expirationDateForPremium,@accountTypeSelected,@paymentStatus,@userFullName,@paymentID,@receiptID,@planID)";
                comm.Parameters.AddWithValue("@UserID", user.userID);
                comm.Parameters.AddWithValue("@Username", user.username);
                comm.Parameters.AddWithValue("@Password", user.password);
                comm.Parameters.AddWithValue("@RetypePassword", user.retypePassword);
                comm.Parameters.AddWithValue("@Email", user.email);
                comm.Parameters.AddWithValue("@ExpirationDateForPremium", user.expirationDateForPremium);
                comm.Parameters.AddWithValue("@AccountTypeSelected", user.accountTypeSelected);
                comm.Parameters.AddWithValue("@PaymentStatus", user.paymentStatus);
                comm.Parameters.AddWithValue("@UserFullName", user.userFullName);
                comm.Parameters.AddWithValue("@PaymentID", user.paymentID);
                comm.Parameters.AddWithValue("@ReceiptID", user.receiptID);
                comm.Parameters.AddWithValue("@PlanID", user.planID);
                rowsinserted = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsinserted;
        }

        public static int UpdateUser(UserAccount user)
        {
            int rowsupdated = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE useraccount SET Username=@userName,Password=@password,Email=@email,ExpirationDateForPremium=@expirationDateForPremium,AccountTypeSelected=@accountTypeSelected,UserFullName=@userFullName where Username=@username";
                comm.Parameters.AddWithValue("@username", user.username);
                comm.Parameters.AddWithValue("@password", user.password);
                comm.Parameters.AddWithValue("@email", user.email);
                comm.Parameters.AddWithValue("@expirationDateForPremium", user.expirationDateForPremium);
                comm.Parameters.AddWithValue("@accountTypeSelected", user.accountTypeSelected);
                comm.Parameters.AddWithValue("@userFullName", user.userFullName);
                rowsupdated = comm.ExecuteNonQuery();

                conn.Close();

            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsupdated;

        }

        public static int InsertSurvey(Survey survey)
        {
            int rowsinserted = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO Survey" +
                    " VALUES (@Title,@DateCreated,@DateModified,@UserID,@TypeOfSurvey,@SurveyID,@ExpirationDateOfSurvey,@NoOfRespondents)";
                comm.Parameters.AddWithValue("@Title", survey.title);
                comm.Parameters.AddWithValue("@DateCreated", survey.dateCreated);
                comm.Parameters.AddWithValue("@DateModified", survey.dateModified);
                comm.Parameters.AddWithValue("@UserID", survey.userID);
                comm.Parameters.AddWithValue("@TypeOfSurvey", survey.typeOfSurvey);
                comm.Parameters.AddWithValue("@SurveyID", survey.surveyID);
                comm.Parameters.AddWithValue("@ExpirationDateOfSurvey", survey.expirationDateOfSurvey);
                comm.Parameters.AddWithValue("@NoOfRespondents", survey.noOfRespondents);

                rowsinserted = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsinserted;
        }

        public static Survey GetCurrentSurvey(string userID)
        {
            Survey survey = new Survey();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from Survey WHERE UserID='" + userID + "'";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    survey.title = (string)dr["Title"];
                    survey.dateCreated = (string)dr["DateCreated"];
                    survey.dateModified = (string)dr["DateModified"];
                    survey.userID = (string)dr["SurveyID"];
                    survey.typeOfSurvey = (string)dr["TypeOfSurvey"];
                    survey.surveyID = (string)dr["SurveyID"];
                    survey.expirationDateOfSurvey = (string)dr["ExpirationDateOfSurvey"];
                    survey.noOfRespondents = (string)dr["NoOfRespondents"];

                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return survey;
        }


        public static ArrayList GetListOfQuestions(string surveyID, string userID)
        {
            Question question = new Question();
            ArrayList listOfQuestion = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from Question WHERE UserID= @userID and SurveyID= @surveyID ORDER BY QuestionOrder ASC";
                comm.Parameters.AddWithValue("@UserID", userID);
                comm.Parameters.AddWithValue("@SurveyID", surveyID);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    question = new Question();
                    question.questionID = (string)dr["QuestionID"];
                    question.typeOfQuestion = (string)dr["TypeOfQuestion"];
                    question.surveyID = (string)dr["SurveyID"];
                    question.questionsAsked = (string)dr["QuestionsAsked"];
                    question.userID = (string)dr["UserID"];
                    question.questionOrder = Convert.ToString(dr["QuestionOrder"]);
                    listOfQuestion.Add(question);

                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return listOfQuestion;
        }

        public static ArrayList GetSurveyByUserID(string userID)
        {
            Survey survey = new Survey();
            ArrayList listOfSurvey = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from Survey WHERE UserID= @userID";
                comm.Parameters.AddWithValue("@userID", userID);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    survey = new Survey();
                    survey.title = (string)dr["Title"];
                    survey.dateCreated = (string)dr["DateCreated"];
                    survey.dateModified = (string)dr["DateModified"];
                    survey.userID = (string)dr["UserID"];
                    survey.typeOfSurvey = (string)dr["TypeOfSurvey"];
                    survey.surveyID = (string)dr["SurveyID"];
                    survey.expirationDateOfSurvey = (string)dr["ExpirationDateOfSurvey"];
                    survey.noOfRespondents = (string)dr["NoOfRespondents"];
                    listOfSurvey.Add(survey);

                }
                dr.Close();
                conn.Close();

            }
            catch (SqlException e)
            {
                throw e;
            }
            return listOfSurvey;


        }

        public static ArrayList GetAllUserByAccountType(string accountTypeSelected)
        {
            UserAccount user = new UserAccount();
            ArrayList listOfUser = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from UserAccount WHERE AccountTypeSelected= '" + accountTypeSelected + "Basic" + "' OR  AccountTypeSelected= '" + accountTypeSelected + "Premium" + "'";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    user = new UserAccount();
                    user.userID = (string)dr["UserID"];
                    user.username = (string)dr["Username"];
                    user.password = (string)dr["Password"];
                    user.retypePassword = (string)dr["RetypePassword"];
                    user.email = (string)dr["Email"];
                    user.accountTypeSelected = (string)dr["AccountTypeSelected"];
                    user.paymentStatus = (string)dr["PaymentStatus"];
                    user.userFullName = (string)dr["UserFullName"];
                    user.nric = (string)dr["NRIC"];
                    user.paymentID = (string)dr["PaymentID"];
                    user.receiptID = (string)dr["ReceiptID"];
                    user.planID = (string)dr["PlanID"];

                    listOfUser.Add(user);

                }
                dr.Close();
                conn.Close();

            }
            catch (SqlException e)
            {
                throw e;
            }
            return listOfUser;

        }


        public static int InsertAnswer(Answer answer, Question question, Survey survey, UserAccount user)
        {
            int rowsinserted = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO Answer" +
                    " VALUES (@AnswerID,@QuestionID,@SurveyID,@UserID,@AnswerText)";
                comm.Parameters.AddWithValue("@AnswerID", answer.answerID);
                comm.Parameters.AddWithValue("@QuestionID", answer.questionID);
                comm.Parameters.AddWithValue("@SurveyID", answer.surveyID);
                comm.Parameters.AddWithValue("@UserID", answer.userID);
                comm.Parameters.AddWithValue("@AnswerText", answer.answerText);
                rowsinserted = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsinserted;
        }

        public static int InsertQuestion(Question question, Survey survey, UserAccount user)
        {
            int rowsinserted = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO Question" +
                    " VALUES (@QuestionID,@TypeOfQuestion,@SurveyID,@QuestionsAsked,@UserID,@QuestionOrder)";
                comm.Parameters.AddWithValue("@QuestionID", question.questionID);
                comm.Parameters.AddWithValue("@TypeOfQuestion", question.typeOfQuestion);
                comm.Parameters.AddWithValue("@SurveyID", question.surveyID);
                comm.Parameters.AddWithValue("@QuestionsAsked", question.questionsAsked);
                comm.Parameters.AddWithValue("@UserID", question.userID);
                comm.Parameters.AddWithValue("@QuestionOrder", question.questionOrder);
                rowsinserted = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsinserted;
        }

        public static CurrentMaxIDNumber GetCurrentMaxIDNumber(string userID)
        {
            CurrentMaxIDNumber currentMax = new CurrentMaxIDNumber();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from CurrentMaxIDNumber WHERE UserID= '" + userID + "'";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    currentMax.userID = (string)dr["UserID"];
                    currentMax.currentMaxSurveyID = Convert.ToInt64(dr["CurrentMaxSurveyID"]);
                    currentMax.currentMaxQuestionID = Convert.ToInt64(dr["CurrentMaxQuestionID"]);
                    currentMax.currentMaxAnswerID = Convert.ToInt64(dr["CurrentMaxAnswerID"]);
                    currentMax.currentMaxConditionID = Convert.ToInt64(dr["CurrentMaxConditionID"]);

                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return currentMax;
        }

        public static int InsertCurrentMaxIDNumber(CurrentMaxIDNumber currentMax)
        {
            int rowsinserted = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO currentmaxidnumber(userid,currentmaxsurveyid,currentmaxquestionid,currentmaxanswerid,currentmaxconditionid)" +
                    " VALUES (@userID,@currentMaxSurveyID,@currentMaxQuestionID,@currentMaxAnswerID,@currentMaxConditionID)";
                comm.Parameters.AddWithValue("@userID", currentMax.userID);
                comm.Parameters.AddWithValue("@currentMaxSurveyID", currentMax.currentMaxSurveyID);
                comm.Parameters.AddWithValue("@currentMaxQuestionID", currentMax.currentMaxQuestionID);
                comm.Parameters.AddWithValue("@currentMaxAnswerID", currentMax.currentMaxAnswerID);
                comm.Parameters.AddWithValue("@currentMaxConditionID", currentMax.currentMaxConditionID);
                rowsinserted = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsinserted;
        }

        public static int UpdateCurrentMaxIDNumber(CurrentMaxIDNumber currentMax)
        {
            int rowsupdated = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE currentMaxIDNumber SET UserID=@userID,CurrentMaxSurveyID=@currentMaxSurveyID,CurrentMaxQuestionID=@currentMaxQuestionID,CurrentMaxAnswerID=@currentMaxAnswerID,CurrentMaxConditionID=@currentMaxConditionID where UserID=@userID";
                comm.Parameters.AddWithValue("@UserID", currentMax.userID);
                comm.Parameters.AddWithValue("@CurrentMaxSurveyID", currentMax.currentMaxSurveyID);
                comm.Parameters.AddWithValue("@CurrentMaxQuestionID", currentMax.currentMaxQuestionID);
                comm.Parameters.AddWithValue("@CurrentMaxAnswerID", currentMax.currentMaxAnswerID);
                comm.Parameters.AddWithValue("@CurrentMaxConditionID", currentMax.currentMaxConditionID);
                rowsupdated = comm.ExecuteNonQuery();

                conn.Close();

            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsupdated;

        }


        public static Answer GetAnswerByQuestionID(string userID, string surveyID, string questionID)
        {
            Answer answer = new Answer();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from Answer WHERE QuestionID= @questionID";
                comm.Parameters.AddWithValue("@userID", userID);
                comm.Parameters.AddWithValue("@surveyID", surveyID);
                comm.Parameters.AddWithValue("@questionID", questionID);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    answer = new Answer();
                    answer.answerID = (string)dr["AnswerID"];
                    answer.questionID = (string)dr["QuestionID"];
                    answer.surveyID = (string)dr["SurveyID"];
                    answer.userID = (string)dr["UserID"];
                    answer.answerText = (string)dr["AnswerText"];


                }
                dr.Close();
                conn.Close();

            }
            catch (SqlException e)
            {
                throw e;
            }
            return answer;
        }

        public static ArrayList GetListOfAnswer(string surveyID, string userID, string questionID)
        {
            Answer answer = new Answer();
            ArrayList listOfAnswer = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from Answer WHERE UserID= @userID AND SurveyID= @surveyID AND QuestionID=@questionID";
                comm.Parameters.AddWithValue("@UserID", userID);
                comm.Parameters.AddWithValue("@SurveyID", surveyID);
                comm.Parameters.AddWithValue("@QuestionID", questionID);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    answer = new Answer();
                    answer.answerID = (string)dr["AnswerID"];
                    answer.userID = (string)dr["UserID"];
                    answer.surveyID = (string)dr["SurveyID"];
                    answer.questionID = (string)dr["QuestionID"];
                    answer.answerText = (string)dr["AnswerText"];
                    listOfAnswer.Add(answer);

                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return listOfAnswer;
        }

        public static int DeleteAnswer(string answerID, string questionID, string surveyID, string userID)
        {
            int rowsdeleted = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE from Answer WHERE AnswerID= @answerID AND UserID= @userID AND SurveyID= @surveyID AND QuestionID=@questionID";
                comm.Parameters.AddWithValue("@UserID", userID);
                comm.Parameters.AddWithValue("@SurveyID", surveyID);
                comm.Parameters.AddWithValue("@QuestionID", questionID);
                comm.Parameters.AddWithValue("@AnswerID", answerID);
                rowsdeleted = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsdeleted;
        }

        public static int UpdateAnswer(string surveyID, string userID, string questionID, string answerID, string answerText)
        {
            int rowsupdated = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Answer SET AnswerID= @answerID, UserID= @userID, SurveyID= @surveyID, QuestionID=@questionID, AnswerText=@answerText where AnswerID=@answerID";
                comm.Parameters.AddWithValue("@AnswerID", answerID);
                comm.Parameters.AddWithValue("@UserID", userID);
                comm.Parameters.AddWithValue("@SurveyID", surveyID);
                comm.Parameters.AddWithValue("@QuestionID", questionID);
                comm.Parameters.AddWithValue("@AnswerText", answerText);
                rowsupdated = comm.ExecuteNonQuery();

                conn.Close();

            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsupdated;

        }

        public static int UpdateQuestion(string userID, string surveyID, string questionID, string questionsAsked)
        {
            int rowsupdated = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Question SET QuestionID= @questionID, UserID= @userID, SurveyID= @surveyID, QuestionsAsked= @questionsAsked where QuestionID= @questionID";
                comm.Parameters.AddWithValue("@QuestionID", questionID);
                comm.Parameters.AddWithValue("@UserID", userID);
                comm.Parameters.AddWithValue("@SurveyID", surveyID);
                comm.Parameters.AddWithValue("@QuestionsAsked", questionsAsked);
                rowsupdated = comm.ExecuteNonQuery();

                conn.Close();

            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsupdated;

        }

        public static int DeleteQuestion(string userID, string surveyID, string questionID)
        {
            int rowsdeleted = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE from Question WHERE QuestionID= @questionID AND UserID= @userID AND SurveyID= @surveyID";
                comm.Parameters.AddWithValue("@UserID", userID);
                comm.Parameters.AddWithValue("@SurveyID", surveyID);
                comm.Parameters.AddWithValue("@QuestionID", questionID);
                rowsdeleted = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsdeleted;
        }


        public static int UpdateQuestionOrder(string userID, string surveyID, string questionID, string questionOrder)
        {
            int rowsupdated = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Question SET QuestionOrder= @questionOrder, UserID= @userID, SurveyID= @surveyID, QuestionID= @questionID where UserID = @userID AND SurveyID=@surveyID AND QuestionID=@questionID";

                comm.Parameters.AddWithValue("@UserID", userID);
                comm.Parameters.AddWithValue("@SurveyID", surveyID);
                comm.Parameters.AddWithValue("@QuestionID", questionID);
                comm.Parameters.AddWithValue("@QuestionOrder", questionOrder);
                rowsupdated = comm.ExecuteNonQuery();

                conn.Close();

            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsupdated;

        }

        public static int InsertUserAnswer(UserAnswer answer, Question question, Survey survey, UserAccount user)
        {
            int rowsinserted = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO UserAnswer" +
                     " VALUES (@AnswerID,@QuestionID,@SurveyID,@UserID,@SelectedAnswerText)";
                comm.Parameters.AddWithValue("@AnswerID", answer.answerID);
                comm.Parameters.AddWithValue("@QuestionID", answer.questionID);
                comm.Parameters.AddWithValue("@SurveyID", answer.surveyID);
                comm.Parameters.AddWithValue("@UserID", answer.userID);
                comm.Parameters.AddWithValue("@SelectedAnswerText", answer.answerText);
                rowsinserted = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsinserted;
        }

        public static int InsertUserCompleteSurvey(UserAccount user, Survey survey)
        {
            int rowsinserted = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO UserCompleteSurvey" +
                     " VALUES (@UserID,@SurveyID)";
                comm.Parameters.AddWithValue("@UserID", user.userID);
                comm.Parameters.AddWithValue("@SurveyID", survey.surveyID);
                rowsinserted = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsinserted;
        }

        public static ArrayList GetListOfUserAnswers(string surveyID, string userID, string questionID)
        {
            UserAnswer useranswer = new UserAnswer();
            ArrayList listOfUserAnswer = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from UserAnswer WHERE UserID= @userID AND SurveyID= @surveyID AND QuestionID=@questionID";
                comm.Parameters.AddWithValue("@UserID", userID);
                comm.Parameters.AddWithValue("@SurveyID", surveyID);
                comm.Parameters.AddWithValue("@QuestionID", questionID);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    useranswer = new UserAnswer();
                    useranswer.answerID = (string)dr["AnswerID"];
                    useranswer.userID = (string)dr["UserID"];
                    useranswer.surveyID = (string)dr["SurveyID"];
                    useranswer.questionID = (string)dr["QuestionID"];
                    useranswer.answerText = (string)dr["AnswerText"];
                    listOfUserAnswer.Add(useranswer);

                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return listOfUserAnswer;
        }

        public static int UpdateUserAnswer(string surveyID, string userID, string questionID, string answerID, string answerText)
        {
            int rowsupdated = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE UserAnswer SET AnswerID= @answerID, UserID= @userID, SurveyID= @surveyID, QuestionID=@questionID, AnswerText=@answerText where AnswerID=@answerID";
                comm.Parameters.AddWithValue("@AnswerID", answerID);
                comm.Parameters.AddWithValue("@UserID", userID);
                comm.Parameters.AddWithValue("@SurveyID", surveyID);
                comm.Parameters.AddWithValue("@QuestionID", questionID);
                comm.Parameters.AddWithValue("@AnswerText", answerText);
                rowsupdated = comm.ExecuteNonQuery();

                conn.Close();

            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsupdated;

        }

        public static int DeleteSurvey(Survey survey)
        {
            int rowsdeleted = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE from Survey WHERE SurveyID= @surveyID";
                comm.Parameters.AddWithValue("@SurveyID", survey.surveyID);
                rowsdeleted = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsdeleted;
        }

        public static ArrayList GetAllSurveys()
        {
            Survey survey = new Survey();
            ArrayList listOfSurveys = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from Survey";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    survey = new Survey();
                    survey.title = (string)dr["Title"];
                    survey.dateCreated = (string)dr["DateCreated"];
                    survey.dateModified = (string)dr["DateModified"];
                    survey.userID = (string)dr["UserID"];
                    survey.typeOfSurvey = (string)dr["TypeOfSurvey"];
                    survey.surveyID = (string)dr["SurveyID"];
                    survey.expirationDateOfSurvey = (string)dr["ExpirationDateOfSurvey"];
                    survey.noOfRespondents = (string)dr["NoOfRespondents"];
                    listOfSurveys.Add(survey);

                }
                dr.Close();
                conn.Close();

            }
            catch (SqlException e)
            {
                throw e;
            }
            return listOfSurveys;


        }

        public static int DeleteAnswersBySurveyID(string surveyID)
        {
            int rowsdeleted = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE from Answer WHERE SurveyID= @surveyID";
                comm.Parameters.AddWithValue("@SurveyID", surveyID);
                rowsdeleted = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsdeleted;

        }

        public static int DeleteUserAnswersBySurveyID(string surveyID)
        {
            int rowsdeleted = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE from UserAnswer WHERE SurveyID= @surveyID";
                comm.Parameters.AddWithValue("@SurveyID", surveyID);
                rowsdeleted = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsdeleted;

        }

        public static int DeleteQuestionsBySurveyID(string surveyID)
        {
            int rowsdeleted = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE from Question WHERE SurveyID= @surveyID";
                comm.Parameters.AddWithValue("@SurveyID", surveyID);
                rowsdeleted = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsdeleted;

        }

        public static int DeleteUserAnswers(string userid, string surveyid, string questionid)
        {
            int rowsdeleted = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE from UserAnswer WHERE UserID = @userID AND SurveyID=@surveyID AND QuestionID= @questionID";
                comm.Parameters.AddWithValue("@UserID", userid);
                comm.Parameters.AddWithValue("@SurveyID", surveyid);
                comm.Parameters.AddWithValue("@QuestionID", questionid);
                rowsdeleted = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsdeleted;

        }

        public static bool GetUserCompleteSurvey(string userID, string surveyID)
        {
            bool result = false;
            UserCompleteSurvey userCompleteSurvey = new UserCompleteSurvey();

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT UserID, SurveyID FROM UserCompleteSurvey WHERE UserID = @userID AND SurveyID = @surveyID";
                comm.Parameters.AddWithValue("@UserID", userID);
                comm.Parameters.AddWithValue("@SurveyID", surveyID);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    userCompleteSurvey.userID = (string)dr["UserID"];
                    userCompleteSurvey.surveyID = (string)dr["SurveyID"];
                }
                dr.Close();
                conn.Close();

            }
            catch (SqlException e)
            {
                throw e;
            }
            if (userCompleteSurvey.userID == null)
            {
                result = false;
            }
            else
            {
                result = true;
            }

            return result;
        }

        public static int UpdateDateModified(string surveyID, DateTime dateModified)
        {
            int rowsupdated = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Survey SET dateModified= @dateModified where SurveyID = @surveyID";
                comm.Parameters.AddWithValue("@DateModified", DateTime.Now.Year + "/" + DateTime.Now.Month + "/" + DateTime.Now.Day + "-" + DateTime.Now.Hour.ToString() + ":" + DateTime.Now.Minute.ToString() + ":" + DateTime.Now.Second.ToString());
                comm.Parameters.AddWithValue("@SurveyID", surveyID);
                rowsupdated = comm.ExecuteNonQuery();

                conn.Close();

            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsupdated;

        }


        public static string GetEmail(string email)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT Email from UserAccount where Email = @Email";
                comm.Parameters.AddWithValue("@Email", email);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    email = (string)dr["Email"];

                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return email;
        }

        public static string GetUsernameByEmail(string email)
        {
            UserAccount useraccount = new UserAccount();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from UserAccount where Email = @Email";
                comm.Parameters.AddWithValue("@Email", email);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    useraccount.userID = (string)dr["UserID"];
                    useraccount.username = (string)dr["Username"];
                    useraccount.password = (string)dr["Password"];
                    useraccount.retypePassword = (string)dr["RetypePassword"];
                    useraccount.email = (string)dr["Email"];
                    useraccount.expirationDateForPremium = (string)dr["ExpirationDateForPremium"];
                    useraccount.accountTypeSelected = (string)dr["AccountTypeSelected"];
                    useraccount.paymentStatus = (string)dr["PaymentStatus"];
                    useraccount.userFullName = (string)dr["UserFullName"];
                    useraccount.paymentID = (string)dr["PaymentID"];
                    useraccount.receiptID = (string)dr["ReceiptID"];
                    useraccount.planID = (string)dr["PlanID"];

                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return useraccount.username;
        }

        public static int checkIfUsernameExistByEmail(string email)
        {
            int result=0;
            UserAccount useraccount = new UserAccount();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT Username, Email FROM UserAccount WHERE Email = @Email";
                comm.Parameters.AddWithValue("@Email", email);
                SqlDataReader dr = comm.ExecuteReader();
                if(dr.HasRows)
                {
                   result = 1;
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }

        public static int UpdateUserPassword(string password, string email)
        {
            int rowsupdated = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE UserAccount SET Password = @Password where Email = @Email ";
                comm.Parameters.AddWithValue("@Email", email);
                comm.Parameters.AddWithValue("@Password", password);
                rowsupdated = comm.ExecuteNonQuery();

                conn.Close();

            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsupdated;

        }

        public static int checkIfUserAccountExist(string username, string password)
        {
            int result = 0;
            UserAccount useraccount = new UserAccount();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT Username, Password FROM UserAccount WHERE Username = @Username AND Password = @Password";
                comm.Parameters.AddWithValue("@Username", username);
                comm.Parameters.AddWithValue("@Password", password);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.HasRows)
                {
                    result = 1;
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }







        public static int UpdateExpirationDateOfSurvey(string expirationDateOfSurvey, string surveyID)
        {
            int rowsupdated = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Survey SET ExpirationDateOfSurvey = @ExpirationDateOfSurvey where SurveyID = @SurveyID ";
                comm.Parameters.AddWithValue("@SurveyID", surveyID);
                comm.Parameters.AddWithValue("@ExpirationDateOfSurvey", expirationDateOfSurvey);
                rowsupdated = comm.ExecuteNonQuery();

                conn.Close();

            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsupdated;

        }

        public static int InsertExpirationDateOfSurvey(string expirationDateOfSurvey, string surveyID)
        {
            int rowsinserted = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO Survey" +
                     " VALUES (@Title,@DateCreated,@DateModified,@UserID,@TypeOfSurvey,@SurveyID,@ExpirationDateOfSurvey,@NoOfRespondents)";
                comm.Parameters.AddWithValue("@SurveyID", surveyID);
                comm.Parameters.AddWithValue("@ExpirationDateOfSurvey", expirationDateOfSurvey);
                rowsinserted = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsinserted;
        }

        public static int checkIfExpirationDateOfSurveyExists(string expirationDateOfSurvey, string surveyID)
        {
            int result = 0;
            UserAccount useraccount = new UserAccount();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT ExpirationDateOfSurvey, SurveyID FROM Survey WHERE ExpirationDateOfSurvey = @ExpirationDateOfSurvey AND SurveyID = @SurveyID";
                comm.Parameters.AddWithValue("@ExpirationDateOfSurvey", expirationDateOfSurvey);
                comm.Parameters.AddWithValue("@SurveyID", surveyID);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.HasRows)
                {
                    result = 1;
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }


        public static string GetExpirationDateOfSurvey(string surveyID)
        {
            Survey survey = new Survey();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from Survey where SurveyID = @SurveyID";
                comm.Parameters.AddWithValue("@SurveyID", surveyID);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    survey = new Survey(); 
                    survey.title = (string)dr["Title"];
                    survey.dateCreated = (string)dr["DateCreated"];
                    survey.dateModified = (string)dr["DateModified"];
                    survey.userID = (string)dr["UserID"];
                    survey.typeOfSurvey = (string)dr["TypeOfSurvey"];
                    survey.surveyID = (string)dr["SurveyID"];
                    survey.expirationDateOfSurvey = (string)dr["ExpirationDateOfSurvey"];
                    survey.noOfRespondents = (string)dr["NoOfRespondents"];

                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return survey.expirationDateOfSurvey;
        }

        public static ArrayList GetUserAnswersBySurveyID(string surveyID)
        {
            UserAnswer useranswer = new UserAnswer();
            ArrayList listOfUserAnswers = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from UserAnswer WHERE SurveyID= @surveyID";
                comm.Parameters.AddWithValue("@SurveyID", surveyID);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    useranswer = new UserAnswer();
                    useranswer.answerID = (string)dr["AnswerID"];
                    useranswer.userID = (string)dr["UserID"];
                    useranswer.surveyID = (string)dr["SurveyID"];
                    useranswer.questionID = (string)dr["QuestionID"];
                    useranswer.answerText = (string)dr["AnswerText"];
                    listOfUserAnswers.Add(useranswer);

                }
                dr.Close();
                conn.Close();

            }
            catch (SqlException e)
            {
                throw e;
            }
            return listOfUserAnswers;


        }

        public static int UpdateTitleOfSurvey(string surveyID, string title)
        {
            int rowsupdated = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Survey SET Title = @Title where SurveyID = @SurveyID";
                comm.Parameters.AddWithValue("@SurveyID", surveyID);
                comm.Parameters.AddWithValue("@Title", title);
                rowsupdated = comm.ExecuteNonQuery();

                conn.Close();

            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsupdated;

        }

        public static string GetTitleOfSurvey(string surveyID)
        {
            Survey survey = new Survey();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from Survey where SurveyID = @surveyID";
                comm.Parameters.AddWithValue("@SurveyID", surveyID);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    survey = new Survey();
                    survey.title = (string)dr["Title"];
                    survey.dateCreated = (string)dr["DateCreated"];
                    survey.dateModified = (string)dr["DateModified"];
                    survey.userID = (string)dr["UserID"];
                    survey.typeOfSurvey = (string)dr["TypeOfSurvey"];
                    survey.surveyID = (string)dr["SurveyID"];
                    survey.expirationDateOfSurvey = (string)dr["ExpirationDateOfSurvey"];
                    survey.noOfRespondents = (string)dr["NoOfRespondents"];
                    
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return survey.title;
        }

        public static int UpdateTitleAndTypeOfSurvey(string surveyID, string title, string typeOfSurvey)
        {
            int rowsupdated = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Survey SET Title = @Title, TypeOfSurvey= @TypeOfSurvey where SurveyID = @SurveyID";
                comm.Parameters.AddWithValue("@SurveyID", surveyID);
                comm.Parameters.AddWithValue("@Title", title);
                comm.Parameters.AddWithValue("@TypeOfSurvey", typeOfSurvey);
                rowsupdated = comm.ExecuteNonQuery();

                conn.Close();

            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsupdated;

        }

        public static string GetTypeOfSurvey(string surveyID)
        {
            Survey survey = new Survey();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from Survey where SurveyID = @surveyID";
                comm.Parameters.AddWithValue("@SurveyID", surveyID);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    survey = new Survey();
                    survey.title = (string)dr["Title"];
                    survey.dateCreated = (string)dr["DateCreated"];
                    survey.dateModified = (string)dr["DateModified"];
                    survey.userID = (string)dr["UserID"];
                    survey.typeOfSurvey = (string)dr["TypeOfSurvey"];
                    survey.surveyID = (string)dr["SurveyID"];
                    survey.expirationDateOfSurvey = (string)dr["ExpirationDateOfSurvey"];
                    survey.noOfRespondents = (string)dr["NoOfRespondents"];

                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return survey.typeOfSurvey;
        }

        public static int UpdateTypeOfSurvey(string surveyID, string typeOfSurvey)
        {
            int rowsupdated = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Survey SET TypeOfSurvey = @TypeOfSurvey where SurveyID = @SurveyID";
                comm.Parameters.AddWithValue("@SurveyID", surveyID);
                comm.Parameters.AddWithValue("@TypeOfSurvey", typeOfSurvey);
                rowsupdated = comm.ExecuteNonQuery();

                conn.Close();

            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsupdated;

        }


        public static ArrayList GetListOfConditions(string surveyID)
        {
            InformationConditionConsent informationConditionConsent = new InformationConditionConsent();
            ArrayList listOfConditions = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from InformationConditionConsent WHERE SurveyID= @surveyID";
                comm.Parameters.AddWithValue("@SurveyID", surveyID);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    informationConditionConsent = new InformationConditionConsent();
                    informationConditionConsent.surveyID = (string)dr["SurveyID"];
                    informationConditionConsent.checkboxText = (string)dr["CheckboxText"];
                    informationConditionConsent.conditionID = (string)dr["ConditionID"];
                    listOfConditions.Add(informationConditionConsent);

                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return listOfConditions;
        }

        public static int UpdateInformationConditionConsent(string surveyID, string conditionID, string checkBoxText)
        {
            int rowsupdated = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE InformationConditionConsent SET CheckBoxText = @CheckBoxText  where SurveyID=@SurveyID AND ConditionID=@ConditionID";
                comm.Parameters.AddWithValue("@SurveyID", surveyID);
                comm.Parameters.AddWithValue("@ConditionID", conditionID);
                comm.Parameters.AddWithValue("@CheckBoxText", checkBoxText);
                rowsupdated = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsupdated;
        }


        public static int UpdateInformationConsentAndSheet(string surveyID, string imageURL, string informationSheetText, string informationConsentForm)
        {
            int rowsupdated = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE InformationConsentAndSheet SET SurveyID=@SurveyID, ImageURL = @ImageURL, InformationSheetText = @InformationSheetText, InformationConsentForm=@InformationConsentForm where SurveyID=@SurveyID";
                comm.Parameters.AddWithValue("@SurveyID", surveyID);
                comm.Parameters.AddWithValue("@ImageURL", imageURL);
                comm.Parameters.AddWithValue("@informationSheetText", informationSheetText);
                comm.Parameters.AddWithValue("@informationConsentForm", informationConsentForm);
                rowsupdated = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsupdated;
        }

        public static int InsertConditions(InformationConditionConsent infoConditionConsent, string surveyID)
        {
            int rowsinserted = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO InformationConditionConsent" +
                    " VALUES (@SurveyID,@CheckBoxText,@ConditionID)";
                comm.Parameters.AddWithValue("@SurveyID", surveyID);
                comm.Parameters.AddWithValue("@CheckBoxText", infoConditionConsent.checkboxText);
                comm.Parameters.AddWithValue("@ConditionID", infoConditionConsent.conditionID);
                rowsinserted = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsinserted;
        }

        public static int GetCountUserCompleteSurvey(string surveyID)
        {
            int count;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT COUNT (*) from UserCompleteSurvey where surveyID = @surveyID";
                comm.Parameters.AddWithValue("@SurveyID", surveyID);
                count = (int)comm.ExecuteScalar();
               
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return count;
        }

        public static int UpdateNoOfRespondents(string surveyID, string noOfRespondents)
        {
            int rowsupdated = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Survey SET NoOfRespondents=@NoOfRespondents where SurveyID=@SurveyID";
                comm.Parameters.AddWithValue("@NoOfRespondents", noOfRespondents);
                comm.Parameters.AddWithValue("@SurveyID", surveyID);
                rowsupdated = comm.ExecuteNonQuery();

                conn.Close();

            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsupdated;

        }

        public static ArrayList GetInformationConsentAndSheetBySurveyID(string surveyID)
        {
            
            ArrayList listOfInfoSheet = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from InformationConsentAndSheet WHERE SurveyID= @surveyID";
                comm.Parameters.AddWithValue("@SurveyID", surveyID);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    InformationConsentAndSheet infoSheet = new InformationConsentAndSheet();
                    infoSheet.surveyID = (string)dr["SurveyID"];
                    infoSheet.imageURL = (string)dr["ImageURL"];
                    infoSheet.informationSheetText = (string)dr["InformationSheetText"];
                    infoSheet.informationConsentForm = (string)dr["InformationConsentForm"];
                    listOfInfoSheet.Add(infoSheet);

                }
                dr.Close();
                conn.Close();

            }
            catch (SqlException e)
            {
                throw e;
            }
            return listOfInfoSheet;


        }

        public static int InsertInformationConditionAndSheet(InformationConsentAndSheet infoConsentSheet)
        {
            int rowsinserted = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO InformationConsentAndSheet" +
                     " VALUES (@SurveyID,@ImageURL,@InformationSheetText,@InformationConsentForm)";
                comm.Parameters.AddWithValue("@SurveyID", infoConsentSheet.surveyID);
                comm.Parameters.AddWithValue("@ImageURL", infoConsentSheet.imageURL);
                comm.Parameters.AddWithValue("@InformationSheetText", infoConsentSheet.informationSheetText);
                comm.Parameters.AddWithValue("@InformationConsentForm", infoConsentSheet.informationConsentForm);
                rowsinserted = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsinserted;
        }

        public static int InsertInformationConditionConsent(InformationConditionConsent infoConditionConsent)
        {
            int rowsinserted = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO InformationConditionConsent" +
                     " VALUES (@SurveyID,@CheckBoxText,@ConditionID)";
                comm.Parameters.AddWithValue("@SurveyID", infoConditionConsent.surveyID);
                comm.Parameters.AddWithValue("@CheckBoxText", infoConditionConsent.checkboxText);
                comm.Parameters.AddWithValue("@ConditionID", infoConditionConsent.conditionID);
                rowsinserted = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsinserted;
        }

        public static int DeleteInformationConditionConsent(string surveyID, string conditionID)
        {
            int rowsdeleted = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE from InformationConditionConsent WHERE SurveyID=@surveyID AND ConditionID= @conditionID";
                comm.Parameters.AddWithValue("@SurveyID", surveyID);
                comm.Parameters.AddWithValue("@ConditionID", conditionID);
                rowsdeleted = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsdeleted;

        }


        public static string GetImageURL(string surveyID)
        {
            InformationConsentAndSheet infoConsentAndSheet = new InformationConsentAndSheet();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from InformationConsentAndSheet where SurveyID = @SurveyID";
                comm.Parameters.AddWithValue("@SurveyID", surveyID);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    infoConsentAndSheet = new InformationConsentAndSheet();
                    infoConsentAndSheet.surveyID = (string)dr["SurveyID"];
                    infoConsentAndSheet.imageURL = (string)dr["ImageURL"];
                    infoConsentAndSheet.informationSheetText = (string)dr["InformationSheetText"];
                    infoConsentAndSheet.informationConsentForm = (string)dr["InformationConsentForm"];

                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return infoConsentAndSheet.imageURL;
        }

        public static string GetInformationSheetText(string surveyID)
        {
            InformationConsentAndSheet infoConsentAndSheet = new InformationConsentAndSheet();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from InformationConsentAndSheet where SurveyID = @SurveyID";
                comm.Parameters.AddWithValue("@SurveyID", surveyID);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    infoConsentAndSheet = new InformationConsentAndSheet();
                    infoConsentAndSheet.surveyID = (string)dr["SurveyID"];
                    infoConsentAndSheet.imageURL = (string)dr["ImageURL"];
                    infoConsentAndSheet.informationSheetText = (string)dr["InformationSheetText"];
                    infoConsentAndSheet.informationConsentForm = (string)dr["InformationConsentForm"];

                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return infoConsentAndSheet.informationSheetText;
        }

        public static string GetInformationConsentForm(string surveyID)
        {
            InformationConsentAndSheet infoConsentAndSheet = new InformationConsentAndSheet();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from InformationConsentAndSheet where SurveyID = @SurveyID";
                comm.Parameters.AddWithValue("@SurveyID", surveyID);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    infoConsentAndSheet = new InformationConsentAndSheet();
                    infoConsentAndSheet.surveyID = (string)dr["SurveyID"];
                    infoConsentAndSheet.imageURL = (string)dr["ImageURL"];
                    infoConsentAndSheet.informationSheetText = (string)dr["InformationSheetText"];
                    infoConsentAndSheet.informationConsentForm = (string)dr["InformationConsentForm"];

                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return infoConsentAndSheet.informationConsentForm;
        }

        public static string GetAccountType(string username)
        {
            UserAccount useraccount = new UserAccount();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT AccountTypeSelected from UserAccount where Username = @username";
                comm.Parameters.AddWithValue("@Username", username);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    useraccount = new UserAccount();
                    //useraccount.userID = (string)dr["UserID"];
                    //useraccount.username = (string)dr["Username"];
                    //useraccount.password = (string)dr["Password"];
                    //useraccount.retypePassword = (string)dr["RetypePassword"];
                    //useraccount.email = (string)dr["Email"];
                    //useraccount.expirationDateForPremium = (string)dr["ExpirationDateForPremium"];
                    useraccount.accountTypeSelected = (string)dr["AccountTypeSelected"];
                    //useraccount.paymentStatus = (string)dr["PaymentStatus"];
                    //useraccount.userFullName = (string)dr["userFullName"];
                    //useraccount.paymentID = (string)dr["paymentID"];
                    //useraccount.receiptID = (string)dr["receiptID"];
                    //useraccount.planID = (string)dr["PlanID"];

                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return useraccount.accountTypeSelected;
        }

        public static string GetExpirationDateForPremium(string username)
        {
            UserAccount useraccount = new UserAccount();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT ExpirationDateForPremium from UserAccount where Username = @username";
                comm.Parameters.AddWithValue("@Username", username);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    useraccount = new UserAccount();
                    //useraccount.userID = (string)dr["UserID"];
                    //useraccount.username = (string)dr["Username"];
                    //useraccount.password = (string)dr["Password"];
                    //useraccount.retypePassword = (string)dr["RetypePassword"];
                    //useraccount.email = (string)dr["Email"];
                    useraccount.expirationDateForPremium = (string)dr["ExpirationDateForPremium"];
                    //useraccount.accountTypeSelected = (string)dr["AccountTypeSelected"];
                    //useraccount.paymentStatus = (string)dr["PaymentStatus"];
                    //useraccount.userFullName = (string)dr["userFullName"];
                    //useraccount.paymentID = (string)dr["paymentID"];
                    //useraccount.receiptID = (string)dr["receiptID"];
                    //useraccount.planID = (string)dr["PlanID"];

                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return useraccount.expirationDateForPremium;
        }

        public static string GetUsername(string username)
        {
            UserAccount useraccount = new UserAccount();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT Username from UserAccount where Username = @username";
                comm.Parameters.AddWithValue("@Username", username);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    useraccount = new UserAccount();
                    //useraccount.userID = (string)dr["UserID"];
                    useraccount.username = (string)dr["Username"];
                    //useraccount.password = (string)dr["Password"];
                    //useraccount.retypePassword = (string)dr["RetypePassword"];
                    //useraccount.email = (string)dr["Email"];
                    //useraccount.expirationDateForPremium = (string)dr["ExpirationDateForPremium"];
                    //useraccount.accountTypeSelected = (string)dr["AccountTypeSelected"];
                    //useraccount.paymentStatus = (string)dr["PaymentStatus"];
                    //useraccount.userFullName = (string)dr["userFullName"];
                    //useraccount.paymentID = (string)dr["paymentID"];
                    //useraccount.receiptID = (string)dr["receiptID"];
                    //useraccount.planID = (string)dr["PlanID"];

                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return useraccount.username;
        }


        public static string GetPassword(string username)
        {
            UserAccount useraccount = new UserAccount();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT Password from UserAccount where Username = @username";
                comm.Parameters.AddWithValue("@Username", username);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    useraccount = new UserAccount();
                    //useraccount.userID = (string)dr["UserID"];
                    //useraccount.username = (string)dr["Username"];
                    useraccount.password = (string)dr["Password"];
                    //useraccount.retypePassword = (string)dr["RetypePassword"];
                    //useraccount.email = (string)dr["Email"];
                    //useraccount.expirationDateForPremium = (string)dr["ExpirationDateForPremium"];
                    //useraccount.accountTypeSelected = (string)dr["AccountTypeSelected"];
                    //useraccount.paymentStatus = (string)dr["PaymentStatus"];
                    //useraccount.userFullName = (string)dr["userFullName"];
                    //useraccount.paymentID = (string)dr["paymentID"];
                    //useraccount.receiptID = (string)dr["receiptID"];
                    //useraccount.planID = (string)dr["PlanID"];

                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return useraccount.password;
        }

        public static string GetFullName(string username)
        {
            UserAccount useraccount = new UserAccount();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT UserFullName from UserAccount where Username = @username";
                comm.Parameters.AddWithValue("@Username", username);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    useraccount = new UserAccount();
                    //useraccount.userID = (string)dr["UserID"];
                    //useraccount.username = (string)dr["Username"];
                    //useraccount.password = (string)dr["Password"];
                    //useraccount.retypePassword = (string)dr["RetypePassword"];
                    //useraccount.email = (string)dr["Email"];
                    //useraccount.expirationDateForPremium = (string)dr["ExpirationDateForPremium"];
                    //useraccount.accountTypeSelected = (string)dr["AccountTypeSelected"];
                    //useraccount.paymentStatus = (string)dr["PaymentStatus"];
                    useraccount.userFullName = (string)dr["userFullName"];
                    //useraccount.paymentID = (string)dr["paymentID"];
                    //useraccount.receiptID = (string)dr["receiptID"];
                    //useraccount.planID = (string)dr["PlanID"];

                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return useraccount.userFullName;
        }

        public static string GetEmailByUsername(string username)
        {
            UserAccount useraccount = new UserAccount();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT Email from UserAccount where Username = @username";
                comm.Parameters.AddWithValue("@Username", username);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    useraccount = new UserAccount();
                    //useraccount.userID = (string)dr["UserID"];
                    //useraccount.username = (string)dr["Username"];
                    //useraccount.password = (string)dr["Password"];
                    //useraccount.retypePassword = (string)dr["RetypePassword"];
                    useraccount.email = (string)dr["Email"];
                    //useraccount.expirationDateForPremium = (string)dr["ExpirationDateForPremium"];
                    //useraccount.accountTypeSelected = (string)dr["AccountTypeSelected"];
                    //useraccount.paymentStatus = (string)dr["PaymentStatus"];
                    //useraccount.userFullName = (string)dr["userFullName"];
                    //useraccount.paymentID = (string)dr["paymentID"];
                    //useraccount.receiptID = (string)dr["receiptID"];
                    //useraccount.planID = (string)dr["PlanID"];

                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return useraccount.email;
        }


        public static int countUserCompletedQuestions(string userID, string surveyID) {
            int completedQuestionsCount = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT COUNT(DISTINCT questionID) from UserAnswer where userID = '" + userID + "' AND surveyID = '" + surveyID + "';";
                //comm.Parameters.AddWithValue("@Username", username);
                completedQuestionsCount= (int)comm.ExecuteScalar();
               
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }

            return completedQuestionsCount;
        }




        //public static string GetName(string surveyID)
        //{
        //    InformationConsentAndSheet infoConsentAndSheet = new InformationConsentAndSheet();
        //    SqlConnection conn = null;
        //    try
        //    {
        //        conn = new SqlConnection();
        //        conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
        //        conn.Open();
        //        SqlCommand comm = new SqlCommand();
        //        comm.Connection = conn;
        //        comm.CommandText = "SELECT * from InformationConsentAndSheet where SurveyID = @SurveyID";
        //        comm.Parameters.AddWithValue("@SurveyID", surveyID);
        //        SqlDataReader dr = comm.ExecuteReader();
        //        while (dr.Read())
        //        {
        //            infoConsentAndSheet = new InformationConsentAndSheet();
        //            infoConsentAndSheet.surveyID = (string)dr["SurveyID"];
        //            infoConsentAndSheet.name = (string)dr["Name"];
        //            infoConsentAndSheet.imageURL = (string)dr["ImageURL"];
        //            infoConsentAndSheet.informationSheetText = (string)dr["InformationSheetText"];
        //            infoConsentAndSheet.informationConsentForm = (string)dr["InformationConsentForm"];

        //        }
        //        dr.Close();
        //        conn.Close();
        //    }
        //    catch (SqlException e)
        //    {
        //        throw e;
        //    }
        //    return infoConsentAndSheet.name;
        //}

        //public static string GetDate(string surveyID)
        //{
        //    InformationConsentAndSheet infoConsentAndSheet = new InformationConsentAndSheet();
        //    SqlConnection conn = null;
        //    try
        //    {
        //        conn = new SqlConnection();
        //        conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
        //        conn.Open();
        //        SqlCommand comm = new SqlCommand();
        //        comm.Connection = conn;
        //        comm.CommandText = "SELECT * from InformationConsentAndSheet where SurveyID = @SurveyID";
        //        comm.Parameters.AddWithValue("@SurveyID", surveyID);
        //        SqlDataReader dr = comm.ExecuteReader();
        //        while (dr.Read())
        //        {
        //            infoConsentAndSheet = new InformationConsentAndSheet();
        //            infoConsentAndSheet.surveyID = (string)dr["SurveyID"];
        //            infoConsentAndSheet.name = (string)dr["Name"];
        //            infoConsentAndSheet.imageURL = (string)dr["ImageURL"];
        //            infoConsentAndSheet.informationSheetText = (string)dr["InformationSheetText"];
        //            infoConsentAndSheet.informationConsentForm = (string)dr["InformationConsentForm"];

        //        }
        //        dr.Close();
        //        conn.Close();
        //    }
        //    catch (SqlException e)
        //    {
        //        throw e;
        //    }
        //    return infoConsentAndSheet.date;
        //}
    }
}
